﻿/*
 * 轻量级的O-RM框架
 * 1.关系 对象映射
 *      1.一行数据记录为一个对象  --- 对象具有唯一性
 *      
 * 2.数据库操作有哪些
 *   1.添加新的数据
 *   2.条件删除数据
 *   3.条件查询数据
 *   4.条件更新数据
 *   
 * 3.查询的结果分类
 *      1.单行数据  --- 生成单一对象
 *      2.多行数据  --- 生成对象数组
 *      
 * 4.ORM实现的方式
 *      1.充血模式  --- 使用一个基类来实现           方便状态跟踪
 *      2.贫血模式  --- 使用一个静态反射类来实现     状态跟踪难以实现
 *      
 * 5.ORM的方式
 *      1.代码优先  --- 先有代码和模型再创建数据库
 *      2.数据优先  --- 数据库已经创建起来了使用数据库来创建数据表
 */

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using MuYue.ORM;
using MuYue.ORM.Attributes;



namespace MuYue.ORM
{

    /// <summary>
    /// 贫血模式的ORM实现
    /// </summary>
    public static class FastDb
    {
        #region 反射和相关的操作

        private static readonly DbORMCache cache = DbORMCache.SingltonCreate();

        /// <summary>
        /// 反射解析类信息
        /// </summary>
        private static DbTable GetDbTable<T>() {
            DbTable mapModel;
            var first = cache.GetDbTable<T>(out mapModel);
            if (first)
                CreateTableIfNotExists(mapModel);
            return mapModel;
        }

        /// <summary>
        /// 如果数据库中没有数据表创建新表
        /// </summary>
        /// <param name="tableMapModel"></param>
        private static void CreateTableIfNotExists(DbTable tableMapModel) {
            using (var client = DbFactory.GetDb()) {
                client.ExcuteSql(CreateTable(tableMapModel));
            }
        }


        /// <summary>
        /// 构造创建表的sql
        /// </summary>
        /// <param name="mapModel"></param>
        /// <returns></returns>
        private static string CreateTable(DbTable mapModel) {
            var sb = new StringBuilder();
            sb.AppendFormat("create table if not exists `{0}`(", mapModel.TableName);
            var parts = new List<string>();
            foreach (var model in mapModel.Columns) {
                var column = model.Value;

                if (column.Key != null && column.Key.AutoIncrement && string.IsNullOrEmpty(column.Key.Constraint))
                    model.Value.Key.Constraint = AutoIncrementString();

                var columnName = model.Key;

                var columnType = (column.Key != null && !string.IsNullOrWhiteSpace(column.Key.DbTypeName))
                    ? column.Key.DbTypeName
                    : DbTypeAdapter(column.ThePropertyType);

                var constraint = model.Value.Key == null
                    ? ""
                    : model.Value.Key.Constraint;

                parts.Add(string.Format("`{0}` {1} {2}", columnName, columnType, constraint).Trim());
            }

            sb.Append(string.Join(",", parts));
            sb.Append(")");
            return sb.ToString();
        }

        /// <summary>
        /// 数据自动映射实现
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private static string DbTypeAdapter(Type type) {
            switch (Configuration.DbProvider) {
                case DbType.MySql:
                    return MariaDbTypeAdapter(type);
                case DbType.SQLite:
                    return SQLiteTypeAdapter(type);
            }
            throw new Exception("暂不支持的数据库类型");
        }

        /// <summary>
        /// SQLite数据映射服务
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private static string SQLiteTypeAdapter(Type type) {
            if (type == typeof(bool))
                return "BIT NOT NULL";
            if (type == typeof(bool?))
                return "BIT";
            if (type == typeof(sbyte))
                return "INT8 NOT NULL";
            if (type == typeof(sbyte?))
                return "INT8 SByte";
            if (type == typeof(byte))
                return "UINT8 NOT NULL";
            if (type == typeof(byte?))
                return "UINT8";
            if (type == typeof(short))
                return "INT16 NOT NULL";
            if (type == typeof(short?))
                return "INT16";
            if (type == typeof(ushort))
                return "UINT16 NOT NULL";
            if (type == typeof(ushort?))
                return "UINT16";
            if (type == typeof(int))
                return "INT32 NOT NULL";
            if (type == typeof(int?))
                return "INT32";
            if (type == typeof(uint))
                return "UINT32 NOT NULL";
            if (type == typeof(uint?))
                return "UINT32";
            if (type == typeof(long))
                return "integer NOT NULL";
            if (type == typeof(long?))
                return "integer";
            if (type == typeof(ulong))
                return "UINT64 NOT NULL";
            if (type == typeof(ulong?))
                return "UINT64";
            if (type == typeof(float))
                return "FLOAT NOT NULL";
            if (type == typeof(float?))
                return "FLOAT";
            if (type == typeof(double))
                return "DOUBLE NOT NULL";
            if (type == typeof(double?))
                return "DOUBLE";
            if (type == typeof(decimal))
                return "DECIMAL NOT NULL";
            if (type == typeof(decimal?))
                return "DECIMAL";
            if (type == typeof(char))
                return "TEXT NOT NULL	";
            if (type == typeof(char?) || type == typeof(string))
                return "TEXT";
            if (type == typeof(DateTime))
                return "datetime NOT NULL";
            if (type == typeof(DateTime?))
                return "datetime";
            throw new Exception("未能映射到数据类型上面");
        }

        /// <summary>
        /// 自动增长约束的文本表示 
        /// </summary>
        private static string AutoIncrementString() {
            switch (Configuration.DbProvider) {
                case DbType.MySql:
                    return "primary key auto_increment";
                case DbType.SQLite:
                    return "primary key autoincrement";
            }
            throw new Exception("暂不支持的数据库类型");
        }

        /// <summary>
        /// 数据类型映射
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private static string MariaDbTypeAdapter(Type type) {
            if (type == typeof(bool))
                return "BOOL NOT NULL";
            if (type == typeof(bool?))
                return "BOOL";
            if (type == typeof(sbyte))
                return "TINYINT NOT NULL";
            if (type == typeof(sbyte?))
                return "TINYINT UNSIGNED NOT NULL";
            if (type == typeof(byte?))
                return "TINYINT UNSIGNED";
            if (type == typeof(bool))
                return "BOOL NOT NULL";
            if (type == typeof(bool?))
                return "BOOL";
            if (type == typeof(sbyte))
                return "TINYINT NOT NULL";
            if (type == typeof(sbyte?))
                return "TINYINT";
            if (type == typeof(byte))
                return "TINYINT UNSIGNED NOT NULL";
            if (type == typeof(byte?))
                return "TINYINT UNSIGNED 	";
            if (type == typeof(short))
                return "SMALLINT NOT NULL";
            if (type == typeof(short?))
                return "SMALLINT 	";
            if (type == typeof(ushort))
                return "SMALLINT UNSIGNED NOT NULL";
            if (type == typeof(ushort?))
                return "SMALLINT UNSIGNED";
            if (type == typeof(int))
                return "INT NOT NULL";
            if (type == typeof(int?))
                return "INT";
            if (type == typeof(uint))
                return "INT UNSIGNED NOT NULL";
            if (type == typeof(uint?))
                return "INT UNSIGNED";
            if (type == typeof(long))
                return "BIGINT NOT NULL";
            if (type == typeof(long?))
                return "BIGINT";
            if (type == typeof(ulong))
                return "BIGINT UNSIGNED NOT NULL";
            if (type == typeof(ulong?))
                return "BIGINT UNSIGNED";
            if (type == typeof(float))
                return "FLOAT NOT NULL";	　	//极值溢出
            if (type == typeof(float?))
                return "FLOAT";	　	//极值溢出
            if (type == typeof(double))
                return "DOUBLE NOT NULL";
            if (type == typeof(double?))
                return "DOUBLE";	　	　//
            if (type == typeof(decimal))
                return "DECIMAL NOT NULL";	　	//极值溢出
            if (type == typeof(decimal?))
                return "DECIMAL";	　	//极值溢出
            if (type == typeof(char))
                return "CHARACTER NOT NULL";//中文报错
            if (type == typeof(char?))
                return "CHARACTER";//中文报错
            if (type == typeof(string))
                return "text";
            //if (type == typeof(string))
            //    return "VARCHAR(50) NOT NULL";
            //if (type == typeof(string))
            //    return "VARCHAR(50)";
            if (type == typeof(DateTime))
                return "DATETIME NOT NULL";
            if (type == typeof(DateTime?))
                return "DATETIME";
            if (type.IsEnum)
                return "INT";
            throw new Exception("未能映射到数据类型上面");
        }

        #endregion

        #region Query
        /// <summary>
        /// 查询满足条件的所有数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public static List<T> GetAll<T>(string where, string limit = null) {
            var mapModel = GetDbTable<T>();
            using (var mysql = DbFactory.GetDb()) {
                var sql = string.Format("select * from `{0}` where {1}", mapModel.TableName, where);
                if (limit != null)
                    sql = string.Join(" limit ", sql, limit);
                using (var ds = mysql.QueryDataset(sql)) {
                    using (var reader = ds.CreateDataReader()) {
                        List<T> list = new List<T>();
                        var columns = GetColumns(reader, mapModel);
                        while (reader.Read()) {
                            list.Add(CreatObjectByDbRowData<T>(reader, columns));
                        }
                        return list;
                    }
                }
            }
        }


        /// <summary>
        /// 查询数据库中满足条件的信息数目
        /// 主要用于分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public static int GetCount<T>(string where) {
            var tableMapModel = GetDbTable<T>();
            using (var dbProvider = DbFactory.GetDb()) {
                var val = dbProvider.QuerySingle(string.Format("select count(*) from `{0}` where {1}", tableMapModel.TableName, where));
                return Convert.ToInt32(val);
            }
        }

        /// <summary>
        /// 使用格式化语句来查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="format">格式化语句</param>
        /// <param name="args">参数信息</param>
        /// <returns></returns>
        public static T GetByFormat<T>(string format, params string[] args) {
            return Get<T>(string.Format(format, args));
        }

        /// <summary>
        /// 使用条件命令从数据库查询出Model
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public static T Get<T>(string where) {
            var tableMapModel = GetDbTable<T>();
            var sql = string.Format("select * from `{0}` where {1} limit 1", tableMapModel.TableName, where);
            using (var dbProvider = DbFactory.GetDb()) {
                using (var ds = dbProvider.QueryDataset(sql)) {
                    using (var reader = ds.CreateDataReader()) {
                        var columns = GetColumns(reader, tableMapModel);
                        if (reader.Read()) {
                            return CreatObjectByDbRowData<T>(reader, columns);
                        }
                    }
                }
            }
            return default(T);
        }

        /// <summary>
        /// 获取数据表的字段信息
        /// </summary>
        /// <param name="reader">数据表读取器</param>
        /// <param name="tableMapModel">数据表反射信息</param>
        /// <returns></returns>
        private static DbColumn[] GetColumns(DataTableReader reader, DbTable tableMapModel) {
            var cols = reader.FieldCount;
            var columns = new DbColumn[cols];
            for (int i = 0; i < cols; i++) {
                tableMapModel.GetDbColumn(reader.GetName(i), out columns[i]);
            }
            return columns;
        }


        /// <summary>
        /// 根据数据表字段信息和数据库中读到的信息创建对象
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <param name="reader">数据表读取器</param>
        /// <param name="columns">数据表字段信息映射信息</param>
        /// <returns></returns>
        private static T1 CreatObjectByDbRowData<T1>(DataTableReader reader, DbColumn[] columns) {
            var obj = Activator.CreateInstance(typeof(T1));
            for (var i = 0; i < columns.Length; i++) {
                if (columns[i] != null)
                    columns[i].SetValue(obj, reader.GetValue(i));
            }
            return (T1)obj;
        }
        #endregion

        #region Insert

        /// <summary>
        /// 构造插入sql的插入头
        /// </summary>
        /// <param name="mapModel"></param>
        /// <returns></returns>
        private static StringBuilder GetInsertHeader(DbTable mapModel) {
            var sb = new StringBuilder();
            sb.AppendFormat("insert into `{0}`(", mapModel.TableName);
            foreach (var member in mapModel.Columns) {
                if (member.Value.Key == null || !member.Value.Key.AutoIncrement)
                    sb.AppendFormat("`{0}`,", member.Key);
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(") values");
            return sb;
        }

        /// <summary>
        /// 多行插入SQL构建
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <param name="objList"></param>
        /// <param name="mapModel"></param>
        /// <returns></returns>
        private static string InsertList<T1>(List<T1> objList, DbTable mapModel) {
            var sb = GetInsertHeader(mapModel);
            foreach (var val in objList) {
                sb.Append("(");
                foreach (var member in mapModel.Columns) {
                    if (member.Value.Key == null || !member.Value.Key.AutoIncrement)
                        sb.AppendFormat("'{0}',", member.Value.GetValue(val));
                }
                sb.Remove(sb.Length - 1, 1);
                sb.Append("),");
            }
            sb.Remove(sb.Length - 1, 1);
            return sb.ToString();
        }

        /// <summary>
        /// 单行插入记录构建
        /// </summary>
        /// <typeparam name="T1"></typeparam>
        /// <param name="obj"></param>
        /// <param name="mapModel"></param>
        /// <returns></returns>
        private static string Insert<T1>(T1 obj, DbTable mapModel) {
            var sb = GetInsertHeader(mapModel);
            sb.Append("(");
            foreach (var member in mapModel.Columns) {
                if (member.Value.Key == null || !member.Value.Key.AutoIncrement)
                    sb.AppendFormat("'{0}',", member.Value.GetValue(obj));
            }
            sb.Remove(sb.Length - 1, 1);
            sb.Append(")");
            return sb.ToString();
        }


        /// <summary>
        /// 将对象添加到数据库中
        /// </summary>
        /// <returns></returns>
        public static bool Add<T>(T obj) {
            using (var mysql = DbFactory.GetDb()) {
                return mysql.ExcuteSql(Insert(obj, GetDbTable<T>())) == 1;
            }
        }

        /// <summary>
        /// 多行记录插入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="list"></param>
        /// <returns>返回影响的行数</returns>
        public static int AddList<T>(List<T> list) {
            using (var mysql = DbFactory.GetDb()) {
                return mysql.ExcuteSql(InsertList(list, GetDbTable<T>()));
            }
        }
        #endregion

        #region Update
        /// <summary>
        /// 根据条件更新数据
        /// 这个一般是更新单行数据
        /// </summary>
        /// <typeparam name="T">模型</typeparam>
        /// <param name="obj">对象实例</param>
        /// <param name="columnsUsedByWhere">更新条件</param>
        /// <param name="assignment">更新语句,如果不指定就自动遍历</param>
        public static bool Update<T>(T obj, params string[] columnsUsedByWhere) {
            if (columnsUsedByWhere == null)
                throw new ArgumentNullException("columnsUsedByWhere");
            if (columnsUsedByWhere.Length == 0)
                throw new Exception("请提供用于SQL更新的key键");

            var where = GetWhere(obj, columnsUsedByWhere);
            var set = GetUpdateSetCondition(obj, columnsUsedByWhere);
            var tableName = GetDbTable<T>().TableName;
            return Update(tableName, where, set);
        }

        /// <summary>
        /// 根据条件更新数据
        /// 如果要实现更新多行数据那么自己提供更新的条件和参数
        /// </summary>
        /// <typeparam name="T1">泛型</typeparam>
        /// <param name="obj">实例对象</param>
        /// <param name="where">条件语句</param>
        /// <param name="set">更新语句 不提供自动遍历</param>
        /// <returns></returns>
        private static bool Update(string tableName, string where, string set) {
            if (where == null)
                throw new ArgumentNullException("where");
            if (set == null)
                throw new ArgumentNullException("set");

            var sql = string.Format("update `{0}` set {1} where {2}", tableName, set, where);

            using (var provider = DbFactory.GetDb()) {
                return provider.ExcuteSql(sql) > 0;
            }
        }

        /// <summary>
        /// 使用字段名表来匹配条件
        /// 如果不提供字段表就全文匹配
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="columnsUsedByWhere"></param>
        /// <returns></returns>
        private static string GetWhere<T>(T obj, params string[] columnsUsedByWhere) {
            if (columnsUsedByWhere == null)
                throw new ArgumentNullException("columnsUsedByWhere");
            if (columnsUsedByWhere.Length == 0)
                throw new Exception("请提供用于SQL更新的key键");

            var table = GetDbTable<T>();
            var where = new List<string>();

            foreach (var expr in columnsUsedByWhere) {
                DbColumn column;
                if (table.GetDbColumn(expr, out column)) {
                    where.Add(string.Format("`{0}`='{1}'", expr, column.GetValue(obj)));
                }
            }
            return string.Join(" and ", where);
        }

        /// <summary>
        /// 获取更新的更新信息
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="obj"></param>
        /// <param name="columnsUsedByWhere"></param>
        /// <returns></returns>
        private static string GetUpdateSetCondition<T>(T obj, params string[] columnsUsedByWhere) {
            columnsUsedByWhere = columnsUsedByWhere.Select(t => t.ToLower()).ToArray();
            var parts = new List<string>();
            var table = GetDbTable<T>();
            foreach (var kv in table.Columns) {
                if (kv.Value.Key != null && kv.Value.Key.AutoIncrement)
                    continue;
                if (columnsUsedByWhere == null || !columnsUsedByWhere.Contains(kv.Key)) {
                    parts.Add(string.Format("`{0}`='{1}'", kv.Key, kv.Value.GetValue(obj)));
                }
            }
            return string.Join(" , ", parts);
        }
        #endregion

        #region Delete
        /// <summary>
        /// 删除数据项
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="obj">实例</param>
        /// <param name="columnsUsedByWhere">用来构造where语句的属性名如果不指定则全属性遍历生成</param>
        /// <returns>返回删除命令执行的时候影响到的数据行数</returns>
        public static int Delete<T>(T obj, params string[] columnsUsedByWhere) {
            var tableName = GetDbTable<T>().TableName;
            var where = GetWhere(obj, columnsUsedByWhere);
            var sql = string.Format("delete from `{0}` where {1}", tableName, where);
            using (var provider = DbFactory.GetDb()) {
                return provider.ExcuteSql(sql);
            }
        }
        #endregion
    }
}
